package com.huatai.data.jdbc;

import com.alibaba.druid.pool.DruidDataSource;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.huatai.common.exception.BusinessException;
import com.huatai.common.utils.ListUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.List;
import java.util.Map;

public class DBUtils {

    private static final Logger logger = LoggerFactory.getLogger(DBUtils.class);

    public static List<Map<String, Object>> query(DruidDataSource dataSource, String sql, Object... params) throws BusinessException {
        Connection conn = null;
        PreparedStatement ps = null;
        List<Map<String, Object>> list = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            if (params != null && params.length != 0) {
                for (int i = 0; i < params.length; i++) {
                    Object o = params[i] ;
                    if(o == null) {
                        throw new BusinessException("参数为空") ;
                    }
                    ps.setObject(i+1,o);
                }
            }
            list = Lists.newArrayList();
            ResultSet rs = ps.executeQuery() ;
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> map = Maps.newHashMap();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    String columnName = rsmd.getColumnName(i);
                    map.put(columnName, rs.getObject(columnName));
                }
                list.add(map);
            }
            if (list.toString().length() > 20) {
                logger.info("查询结果:" + list.toString().substring(0, 20) + "......");
            } else {
                logger.info("查询结果:" + list);
            }
        } catch (SQLException se) {
            logger.error("sql执行异常", se);
            throw new BusinessException("sql执行异常");
        } finally {
            try {
                if (conn != null)
                    conn.close();
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                logger.error("sql执行异常", e);
                throw new BusinessException("sql执行异常");
            }
        }
        return list;
    }

    public static void execute(DruidDataSource dataSource, String sql, Object... params) throws BusinessException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            if (params != null && params.length != 0) {
                for (int i = 0; i < params.length; i++) {
                    Object o = params[i] ;
                    if(o == null) {
                        throw new BusinessException("参数为空") ;
                    }
                    ps.setObject(i+1,o);
                }
            }
            ps.execute();
        } catch (SQLException se) {
            logger.error("sql执行异常", se);
            throw new BusinessException("sql执行异常");
        } finally {
            try {
                if (conn != null)
                    conn.close();
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                logger.error("sql执行异常", e);
                throw new BusinessException("sql执行异常");
            }
        }
    }

    public static void insert(DruidDataSource dataSource, String sql, List<JdbcObject> objects) throws BusinessException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            if (ListUtils.isEmpty(objects)) {
                throw new BusinessException("参数为空");
            }
            for (int i = 0; i < objects.size(); i++) {
                if (objects.get(i).getObject() != null) {
                    ps.setObject(i + 1, objects.get(i).getObject());
                } else {
                    ps.setNull(i + 1, objects.get(i).getType());
                }
            }
            ps.execute();
        } catch (SQLException se) {
            logger.error("sql执行异常", se);
            throw new BusinessException("sql执行异常");
        } finally {
            try {
                if (conn != null)
                    conn.close();
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                logger.error("sql执行异常", e);
                throw new BusinessException("sql执行异常");
            }
        }
    }


    public static void insertBatchJdbcObject(DruidDataSource dataSource, String sql, List<List<JdbcObject>> list) throws BusinessException {
        Connection conn = null;
        PreparedStatement ps = null;
        if (ListUtils.isEmpty(list)) {
            throw new BusinessException("参数为空");
        }
        try {
            conn = dataSource.getConnection();
            // 设置手动提交
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            int len = list.size();
            for (int i = 0; i < len; i++) {
                List<JdbcObject> objects = list.get(i);
                if (ListUtils.isEmpty(objects)) {
                    throw new BusinessException("参数为空");
                }
                for (int j = 0; j < objects.size(); j++) {
                    logger.info("objects:" + objects.get(j));
                    if (objects.get(j).getObject() != null) {
                        ps.setObject(j + 1, objects.get(j).getObject());
                    } else {
                        ps.setNull(j + 1, objects.get(j).getType());
                    }
                }
                ps.addBatch();
                // 设置 200 提交
                if ((i != 0 && i % 200 == 0) || i == len - 1) {
                    ps.executeBatch();
                    conn.commit();
                    ps.clearBatch(); // 提交后，Batch清空。
                }
            }
        } catch (SQLException se) {
            logger.error("sql执行异常", se);
            throw new BusinessException("sql执行异常");
        } catch (Exception se) {
            logger.error("系统异常", se);
            throw new BusinessException("系统异常");
        } finally {
            try {
                if (conn != null)
                    conn.close();
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                logger.error("sql执行异常", e);
                throw new BusinessException("sql执行异常");
            }
        }
    }

}
